﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.IO;
using System.Net;
using System.Xml;
using System.Text;
using System.Diagnostics;
using System.Web;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using Microsoft.Office.Core; 

namespace SA2012TBRace
{
	class Program
	{
		private static readonly Dictionary<string, LocationInfo> _CoordInfo = new Dictionary<string, LocationInfo>();
		private static string _Delimiter = "	";
		private static string _TBDatabase = "";
		private static string _CacheDatabaseLocation = "";
		private static string _ReportGeocacheNotFound = "";
		private static DateTime _EndOfRaceDate = new DateTime(2014,12,31);
        private static DateTime _StartOfRaceDate = new DateTime(2014,01,18);
		private static IWebProxy _Proxy;

		private static MemoryCacheDB _CacheDB = null;

		static void Main(string[] args)
		{

            // TODO: process arguments

			_Proxy = WebRequest.GetSystemWebProxy();
			_Proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;
			//_Proxy = new WebProxy("54.235.210.159:80");

            // load the configuration file
			GetConfigSettingsFromDisk();

            // clear the could not find text file
			ClearOutReportGeoCachTextFile();

            // create local cache for storage
			_CacheDB = new MemoryCacheDB(_CacheDatabaseLocation, _ReportGeocacheNotFound);

            // load the locally stored co-ordinate information from disk
			GetCoOrdInfoFromDisk();

            // load travel bug information from database
			var trackables = GetAllTbs();

            // catch if no trackables found
			if (trackables != null)
                // loop through all the trackables
				foreach (var trackable in trackables)
				{
                    // load all the log for the trackables
					var allLogs = GetAllLogsForTrackable(trackable.Code);

					if (allLogs != null)
					{
                        
						trackable.TotalDistance = CalculateTotalDisTanceFromLogs(allLogs);

						var result = CalculateSinglePersonDropDistanceFromLogs(allLogs);
						trackable.SinglePersonDropDistance = result.Item1;
						trackable.CachesDroppedInto = result.Item2;

						trackable.NumberOfCachesVisited = CalculateCachesVisitedFromLogs(allLogs);
						trackable.TimesDiscovered = CalculateTimesDiscoveredFromLogs(allLogs);
						trackable.CountriesVisited = GetAllCountriesVisited(allLogs);
						trackable.StatesVisited = GetAllCountryStatesVisited(allLogs);
						trackable.CitosVisited = GetAllCitosVisitedfromLogs(allLogs);
                        trackable.EventsVisited = GetAllEventsVisitedfromLogs(allLogs);
						trackable.MegasVisited = GetAllMegasVisitedfromLogs(allLogs);
						trackable.TbHotelsVisited = GetAllTBHotelsVisitedfromLogs(allLogs);
					}

                    // output information to the console
					Console.WriteLine("------------------------------------------------------");
					Console.WriteLine("  Code                       : " + trackable.Code);
					Console.WriteLine("  Name                       : " + trackable.Name);
					Console.WriteLine("  RaceType                   : " + trackable.RaceType);
					Console.WriteLine("  Owner                      : " + trackable.Owner);
					Console.WriteLine("  Country                    : " + trackable.Country);
					Console.WriteLine("  State                      : " + trackable.State);
					Console.WriteLine("  County                     : " + trackable.County);
					Console.WriteLine("  Latitude                   : " + trackable.Latitude);
					Console.WriteLine("  Longitude                  : " + trackable.Longitude);
					Console.WriteLine("  TrackableType              : " + trackable.TrackableType);
					Console.WriteLine("  TrackableParentName        : " + trackable.TrackableParentName);
					Console.WriteLine("  TotalDistance              : " + trackable.TotalDistance);
					Console.WriteLine("  SinglePersonDropDistance   : " + trackable.SinglePersonDropDistance);
					Console.WriteLine("  CachesDroppedInto          : " + trackable.CachesDroppedInto);					 
					Console.WriteLine("  NumberOfCachesVisited      : " + trackable.NumberOfCachesVisited);
					Console.WriteLine("  TimesDiscovered            : " + trackable.TimesDiscovered);
					Console.WriteLine("  No times traveled 750 km   : " + trackable.Times750Km);
					Console.WriteLine("  CountriesVisited count     : " + trackable.CountriesVisited.Count());
					Console.WriteLine("  CountriesVisited           : " + String.Join(",", trackable.CountriesVisited.ToArray()));
					Console.WriteLine("  StatesVisited count        : " + trackable.StatesVisited.Count());
					Console.WriteLine("  StatesVisited              : " + String.Join(",", trackable.StatesVisited.ToArray()));
					Console.WriteLine("  CitosVisisted count        : " + trackable.CitosVisited.Count());
                    Console.WriteLine("  EventsVisisted count        : " + trackable.EventsVisited.Count());
					Console.WriteLine("  CitosVisisted              : " + String.Join(",", trackable.CitosVisited.ToArray()));
					Console.WriteLine("  MegasVisited count         : " + trackable.MegasVisited.Count());
					Console.WriteLine("  MegasVisited               : " + String.Join(",", trackable.MegasVisited.ToArray()));
					Console.WriteLine("  TBHotelsVisited count      : " + trackable.TbHotelsVisited.Count());
					Console.WriteLine("  TBHotelsVisited            : " + String.Join(",", trackable.TbHotelsVisited.ToArray()));
					Console.WriteLine("  DiscoveryPoints            : " + trackable.DiscoveryPoints);
					Console.WriteLine("  CacheVisitedPoints         : " +  trackable.CacheVisitedPoints);
					Console.WriteLine("  CountriesVisitedPoint      : " + trackable.CountriesVisitedPoints);
					Console.WriteLine("  CountryStatesVisitedPoints : " + trackable.CountryStatesVisitedPoints);
					Console.WriteLine("  CitosVisistedPoints        : " + trackable.CitosVisistedPoints);
					Console.WriteLine("  MegasVisitedPoints         : " + trackable.MegasVisitedPoints);
					Console.WriteLine("  TBHotelsVisitedPoints      : " + trackable.TbHotelsVisitedPoints);
					Console.WriteLine("  Times750kmPoints           : " + trackable.Times750KmPoints);
					Console.WriteLine("  RaceType                   : " + trackable.RaceType);
					Console.WriteLine("  TotalPoints                : " + trackable.TotalPoints);			
				}

			RaceSummery summery = new RaceSummery();

			summery.TotalDistanceAllRacersInKm = GetTotalRaceDistance(trackables);
			summery.TotalNumberOfCachesVisited = GetTotalNumberOfUniqueCachesVisited(trackables);
			summery.TotalNumberOfCountriesVisisted = GetTotalNumberOfUniqueCountriesVisited(trackables);

            
			PersistCoOrdInfoToDisk();

            // output information files
			WriteInfoToCsvFile(summery);
			WriteInfoToCsvFile(trackables);
			WriteInfoToDetailFile(trackables);
			WriteHtmlToDisk(trackables);
            WriteInfoToJsonFile(trackables);
            outputToExcel(trackables);

		}

        private static void outputToExcel(IEnumerable<Trackable> trackables)
        {
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                Console.WriteLine("EXCEL could not be started. Check that your office installation and project references are correct.");
                return;
            }
            xlApp.Visible = true;

            Workbook wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            Worksheet ws = (Worksheet)wb.Worksheets[1];

            if (ws == null)
            {
                Console.WriteLine("Worksheet could not be created. Check that your office installation and project references are correct.");
            }

            ws.get_Range("A1").Value = "Code";
            ws.get_Range("B1").Value = "Name";
            ws.get_Range("C1").Value = "Owner";
            ws.get_Range("D1").Value = "Country";
            ws.get_Range("E1").Value = "State";
            ws.get_Range("F1").Value = "County";
            ws.get_Range("G1").Value = "Latitude";
            ws.get_Range("H1").Value = "Longitude";
            ws.get_Range("I1").Value = "RaceType";
            ws.get_Range("J1").Value = "TrackableType";
            ws.get_Range("K1").Value = "TrackableParentName";
            ws.get_Range("L1").Value = "TotalDistance";
            ws.get_Range("M1").Value = "SinglePersonDropDistance";
            ws.get_Range("N1").Value = "CachesDroppedInto";
            ws.get_Range("O1").Value = "NumberOfCachesVisited";
            ws.get_Range("P1").Value = "TimesDiscovered";
            ws.get_Range("Q1").Value = "CountriesVisitedCount";
            ws.get_Range("R1").Value = "CountriesVisited";
            ws.get_Range("S1").Value = "StatesVisitedCount";
            ws.get_Range("T1").Value = "StatesVisited";
            ws.get_Range("U1").Value = "EventsVisitedCount";
            ws.get_Range("V1").Value = "EventsVisited";
            ws.get_Range("W1").Value = "CitosVisitedCount";
            ws.get_Range("X1").Value = "CitosVisited";
            ws.get_Range("Y1").Value = "MegasVisitedCount";
            ws.get_Range("Z1").Value = "MegasVisited";
            ws.get_Range("AA1").Value = "TbHotelsVisitedCount";
            ws.get_Range("AB1").Value = "TbHotelsVisited";
            ws.get_Range("AC1").Value = "Times750Km";
            ws.get_Range("AD1").Value = "DiscoveryPoints";
            ws.get_Range("AE1").Value = "CacheVisitedPoints";
            ws.get_Range("AF1").Value = "CountriesVisitedPoints";
            ws.get_Range("AG1").Value = "CountryStatesVisitedPoints";
            ws.get_Range("AH1").Value = "CitosVisistedPoints";
            ws.get_Range("AI1").Value = "MegasVisitedPoints";
            ws.get_Range("AJ1").Value = "EventsVisitedPoints";
            ws.get_Range("AK1").Value = "TbHotelsVisitedPoints";
            ws.get_Range("AL1").Value = "Times100KmPoints";
            ws.get_Range("AM1").Value = "FebPoints";
            ws.get_Range("AN1").Value = "TotalPoints";

            int rowNum = 2;
            foreach (var trackable in trackables)
            {
                ws.get_Range("A" + rowNum).Value = trackable.Code;
                ws.get_Range("B" + rowNum).Value = trackable.Name;
                ws.get_Range("C" + rowNum).Value = trackable.Owner;
                ws.get_Range("D" + rowNum).Value = trackable.Country;
                ws.get_Range("E" + rowNum).Value = trackable.State;
                ws.get_Range("F" + rowNum).Value = trackable.County;
                ws.get_Range("G" + rowNum).Value = trackable.Latitude;
                ws.get_Range("H" + rowNum).Value = trackable.Longitude;
                ws.get_Range("I" + rowNum).Value = trackable.RaceType;
                ws.get_Range("J" + rowNum).Value = trackable.TrackableType;
                ws.get_Range("K" + rowNum).Value = trackable.TrackableParentName;
                ws.get_Range("L" + rowNum).Value = trackable.TotalDistance;
                ws.get_Range("M" + rowNum).Value = trackable.SinglePersonDropDistance;
                ws.get_Range("N" + rowNum).Value = trackable.CachesDroppedInto;
                ws.get_Range("O" + rowNum).Value = trackable.NumberOfCachesVisited;
                ws.get_Range("P" + rowNum).Value = trackable.TimesDiscovered;
                
                try
                {
                    ws.get_Range("R" + rowNum).Value = trackable.CountriesVisited;
                }
                catch { }
                try
                {
                    ws.get_Range("S" + rowNum).Value = trackable.StatesVisited;
                    ws.get_Range("T" + rowNum).Value = trackable.StatesVisited.Count() * 30;
                }
                catch { }

                try
                {
                    ws.get_Range("U" + rowNum).Value = trackable.EventsVisited.Count();
                    ws.get_Range("V" + rowNum).Value = trackable.EventsVisited;
                }
                catch { }
                try
                {
                    ws.get_Range("W" + rowNum).Value = trackable.CitosVisited;
                    ws.get_Range("X" + rowNum).Value = trackable.CitosVisistedPoints;
                }
                catch{ }
                try
                {
                    ws.get_Range("Y" + rowNum).Value = trackable.MegasVisited.Count();
                    ws.get_Range("Z" + rowNum).Value = trackable.MegasVisited;
                }
                catch{}

                try
                {
                    ws.get_Range("AA" + rowNum).Value = trackable.TbHotelsVisited.Count();
                    ws.get_Range("AB" + rowNum).Value = trackable.TbHotelsVisited;
                }
                catch{}

                ws.get_Range("AC" + rowNum).Value = trackable.Times750Km;

                ws.get_Range("AD" + rowNum).Value = trackable.DiscoveryPoints;
                ws.get_Range("AE" + rowNum).Value = trackable.CacheVisitedPoints;
                ws.get_Range("AF" + rowNum).Value = trackable.CountriesVisitedPoints;
                ws.get_Range("AG" + rowNum).Value = trackable.CountryStatesVisitedPoints;
                ws.get_Range("AH" + rowNum).Value = trackable.CitosVisistedPoints;
                ws.get_Range("AI" + rowNum).Value = trackable.MegasVisitedPoints;
                ws.get_Range("AJ" + rowNum).Value = trackable.EventsVisitedPoints;
                ws.get_Range("AK" + rowNum).Value = trackable.TbHotelsVisitedPoints;
                ws.get_Range("AL" + rowNum).Value = trackable.TimesTo100Km;
                //ws.get_Range("AM" + rowNum).Value = trackable.FebPoints;

                ws.get_Range("AN" + rowNum).Formula = "=SUM(AD"+rowNum.ToString() + ":AM"+rowNum.ToString() +")";
                
                rowNum++;
            }
        }

		private static int GetTotalNumberOfUniqueCountriesVisited(IEnumerable<Trackable> trackables)
		{
			var countries = new List<string>();

			foreach (var trackable in trackables)
			{
				foreach (var country in trackable.CountriesVisited)
				{
					if (!countries.Contains(country))
					{
						countries.Add(country);
					}
				}
			}

			return countries.Count;
		}

		private static int GetTotalNumberOfUniqueCachesVisited(IEnumerable<Trackable> trackables)
		{
			var caches = new List<string>();

			foreach (var trackable in trackables)
			{
				var allLogs = GetAllLogsForTrackable(trackable.Code);

				foreach (var itemLog in allLogs)
				{
					if (!caches.Contains(itemLog.Location))
					{
						caches.Add(itemLog.Location);
					}
				}
			}

			return caches.Count;
		}

		private static double GetTotalRaceDistance(IEnumerable<Trackable> trackables)
		{
			return trackables.Sum(trackable => trackable.SinglePersonDropDistance);
		}

        private static void WriteInfoToJsonFile(IEnumerable<Trackable> trackables)
        {
            var fileNameAndLocation = GetExecutingPath() + @"\TBRaceResultsDetail.JSON";
            TextWriter writer = new StreamWriter(fileNameAndLocation);
            writer.WriteLine("{\"trackables\" : [");
            int count = trackables.Count();
            int i = 0;
            foreach (var trackable in trackables)
            {
                writer.WriteLine("{");
                writer.WriteLine("\"Code\" : \"" + trackable.Code +"\",");
                writer.WriteLine("\"Name\" : \"" + trackable.Name + "\",");
                writer.WriteLine("\"Owner\" : \"" + trackable.Owner + "\",");
                writer.WriteLine("\"Country\" : \"" + trackable.Country + "\",");
                writer.WriteLine("\"State\" : \"" + trackable.State + "\",");
                writer.WriteLine("\"County\" : \"" + trackable.County + "\",");
                writer.WriteLine("\"Latitude\" : \"" + trackable.Latitude + "\",");
                writer.WriteLine("\"Longitude\" : \"" + trackable.Longitude + "\",");
                writer.WriteLine("\"RaceType\" : \"" + trackable.RaceType + "\",");
                writer.WriteLine("\"TrackableType\" : \"" + trackable.TrackableType + "\",");
                writer.WriteLine("\"TrackableParentName\" : \"" + trackable.TrackableParentName + "\",");
                writer.WriteLine("\"TotalDistance\" : \"" + trackable.TotalDistance + "\",");
                writer.WriteLine("\"SinglePersonDropDistance\" : \"" + trackable.SinglePersonDropDistance + "\",");
                writer.WriteLine("\"NumberOfCachesVisited\" : \"" + trackable.NumberOfCachesVisited + "\",");
                writer.WriteLine("\"CachesDroppedInto\" : \"" + trackable.CachesDroppedInto + "\",");
                writer.WriteLine("\"TimesDiscoved\" : \"" + trackable.TimesDiscovered + "\",");
                writer.WriteLine("\"Owner\" : \"" + trackable.Owner + "\"");
                // TODO: Complete outputting the additional fields
                writer.WriteLine("}");
                
                // put a comma in between elements except the last one
                if (i != count - 1)
                {
                    writer.WriteLine(",");
                }
                i++;
               
            }
            writer.WriteLine("]}");
            writer.Close();
        }

		private static void WriteInfoToDetailFile(IEnumerable<Trackable> trackables)
		{
			var fileNameAndLocation = GetExecutingPath() + @"\output\TBRaceResultsDetail.txt";

			TextWriter writer = new StreamWriter(fileNameAndLocation);

			foreach (var trackable in trackables)
			{
				writer.WriteLine(trackable.Name + "[" + trackable.Code + "]");
				writer.WriteLine("  Code                       : " + trackable.Code);
				writer.WriteLine("  Name                       : " + trackable.Name);
				writer.WriteLine("  Owner                      : " + trackable.Owner);
				writer.WriteLine("  Country                    : " + trackable.Country);
				writer.WriteLine("  State                      : " + trackable.State);
				writer.WriteLine("  County                     : " + trackable.County);
				writer.WriteLine("  Latitude                   : " + trackable.Latitude);
				writer.WriteLine("  Longitude                  : " + trackable.Longitude);
				writer.WriteLine("  TrackableType              : " + trackable.TrackableType);
				writer.WriteLine("  TrackableParentName        : " + trackable.TrackableParentName);
				writer.WriteLine("  TotalDistance              : " + trackable.TotalDistance);
				writer.WriteLine("  SinglePersonDropDistance   : " + trackable.SinglePersonDropDistance);
				writer.WriteLine("  NumberOfCachesVisited      : " + trackable.NumberOfCachesVisited);
				writer.WriteLine("  CachesDroppedInto          : " + trackable.CachesDroppedInto);
				writer.WriteLine("  TimesDiscovered            : " + trackable.TimesDiscovered);
				writer.WriteLine("  CountriesVisited count     : " + trackable.CountriesVisited.Count());
				writer.WriteLine("  CountriesVisited           : " + String.Join(",", trackable.CountriesVisited));
				writer.WriteLine("  StatesVisited count        : " + trackable.StatesVisited.Count());
				writer.WriteLine("  StatesVisited              : " + String.Join(",", trackable.StatesVisited));
				writer.WriteLine("  CitosVisisted count        : " + trackable.CitosVisited.Count());
                writer.WriteLine("  EventsVisisted count        : " + trackable.EventsVisited.Count());
				writer.WriteLine("  CitosVisisted              : " + String.Join(",", trackable.CitosVisited));
				writer.WriteLine("  MegasVisited count         : " + trackable.MegasVisited.Count());
				writer.WriteLine("  MegasVisited               : " + String.Join(",", trackable.MegasVisited));
				writer.WriteLine("  TBHotelsVisited count      : " + trackable.TbHotelsVisited.Count());
				writer.WriteLine("  TBHotelsVisited            : " + String.Join(",", trackable.TbHotelsVisited));

				writer.WriteLine("  Times750km                 : " + trackable.Times750Km);

				writer.WriteLine("  DiscoveryPoints            : " + trackable.DiscoveryPoints);
				writer.WriteLine("  CacheVisitedPoints         : " + trackable.CacheVisitedPoints);
				writer.WriteLine("  CountriesVisitedPoint      : " + trackable.CountriesVisitedPoints);
				writer.WriteLine("  CountryStatesVisitedPoints : " + trackable.CountryStatesVisitedPoints);
				writer.WriteLine("  CitosVisistedPoints        : " + trackable.CitosVisistedPoints);
				writer.WriteLine("  MegasVisitedPoints         : " + trackable.MegasVisitedPoints);
				writer.WriteLine("  TBHotelsVisitedPoints      : " + trackable.TbHotelsVisitedPoints);
				writer.WriteLine("  Times750kmPoints           : " + trackable.Times750KmPoints);
				writer.WriteLine("  RaceType                   : " + trackable.RaceType);
				writer.WriteLine("  TotalPoints                : " + trackable.TotalPoints);
				
				writer.WriteLine("---------------------------------------------------------------------");
			}

			writer.Close();
		}

		private static void WriteHtmlToDisk(IEnumerable<Trackable> trackables)
		{
			if (trackables != null)
			{
				var pointsTrackables = (
				                       	from t in trackables
				                       	where t.RaceType == "Points"
				                       	select t)
					.OrderByDescending(t => t.TotalPoints);

				var distanceTrackables = (
				                         	from t in trackables
				                         	where t.RaceType == "Distance"
				                         	select t)
					.OrderByDescending(t => t.TotalPoints);

				var htmlSb = new StringBuilder();

				htmlSb.Append(HtmlOutput.GetHtmlHeaderInfo());
				htmlSb.Append(HtmlOutput.GetHtmlPointesInfo(pointsTrackables));
				htmlSb.Append(HtmlOutput.GetHtmlFooterInfo());

				var fileNameAndLocation = GetExecutingPath() + "\\TBRace2013.html";

				var writer = new StreamWriter(fileNameAndLocation);

				writer.Write(htmlSb.ToString());

				writer.Close();
			}
		}

		private static IEnumerable<string> GetAllTBHotelsVisitedfromLogs(IEnumerable<ItemLog> allLogs)
		{
			var cacheLogs = (
				from log in allLogs
				where (
					(log.LogType == "Dropped Off" || log.LogType == "Visited")
					&&
                    log.Date <= _EndOfRaceDate && log.Date >= _StartOfRaceDate
					)
				select log);

			var tbHotelsVisited = new List<string>();

			foreach (var log in cacheLogs)
			{
				var lookupKey = log.Latitude.ToString() + "," + log.Longitude.ToString();

				var gcCode = _CoordInfo[lookupKey].GcCode;

				var isTBHotelCache = _CacheDB.IsTBHotelCache(gcCode);

				if (isTBHotelCache)
					tbHotelsVisited.Add(gcCode);
			}

			return tbHotelsVisited;
		}


        // FEBRUARY 2014 POINTS
        // Double points for caches placed in caches with names containing Love; Red; Hotel; February; Valentine; Heart; Cupid; Honey; Angel; Chocolate
        private static IEnumerable<string> getFebruaryPointsFromLogs(IEnumerable<ItemLog> allLogs)
        {
            var cacheLogs = (
                from log in allLogs
                where (
                    (log.LogType == "Dropped Off" || log.LogType == "Visited")
                    &&
                    log.Date <= new DateTime(2014,02,01) && log.Date >= new DateTime(2014,02,28)
                    )
                select log);

            var tbFebQualifyingGeocachesVisited = new List<string>();

            foreach (var log in cacheLogs)
            {
                var lookupKey = log.Latitude.ToString() + "," + log.Longitude.ToString();

                var gcCode = _CoordInfo[lookupKey].GcCode;

                var isTBFebQualifyingCache = _CacheDB.IsFebruaryQualifyingCache(gcCode);

                if (isTBFebQualifyingCache)
                    tbFebQualifyingGeocachesVisited.Add(gcCode);
            }

            return tbFebQualifyingGeocachesVisited;
        }
  


		private static List<string> GetAllMegasVisitedfromLogs(IEnumerable<ItemLog> allLogs)
		{
			var cacheLogs = (
				from log in allLogs
				where (
					(log.LogType == "Dropped Off" || log.LogType == "Visited")
					&&
                    log.Date <= _EndOfRaceDate && log.Date >= _StartOfRaceDate
					)
				select log);

			var megasVisited = new List<string>();

			foreach (var log in cacheLogs)
			{
				var lookupKey = log.Latitude.ToString() + "," + log.Longitude.ToString();

				var gcCode = _CoordInfo[lookupKey].GcCode;

				var isMegaCache = _CacheDB.IsMegaCache(gcCode);

				if (isMegaCache)
					megasVisited.Add(gcCode);
			}

			return megasVisited;
		}

		private static void ClearOutReportGeoCachTextFile()
		{
			using (var writer = new StreamWriter(_ReportGeocacheNotFound))
			{
				writer.WriteLine("The following caches were not found in the 'Caches-SQLite-DB' database.");
				writer.WriteLine("Please add in these cache types so the program can check what cache type the gc code is.");
			}
		}

		// loads the configuration from a configuration file if it exists.
		private static void GetConfigSettingsFromDisk()
		{
			if (File.Exists(ConfigFileNameAndLocation()))
			{
				using (var reader = new StreamReader(ConfigFileNameAndLocation()))
				{
					while (!reader.EndOfStream)
					{
						var line = reader.ReadLine();

						var values = line.Split('~');

						switch (values[0])
						{
							case "Delimiter":
								if (values[1].ToLower() == "tab")
									_Delimiter = "	";
								else
								{
									_Delimiter = ",";
								}
								break;

							case "TB-SQLite-DB":
								_TBDatabase = values[1].ToLower();
								break;

							case "Caches-SQLite-DB":
								_CacheDatabaseLocation = values[1].ToLower();
								break;

							case "ReportGeocacheNotFound":
								_ReportGeocacheNotFound = values[1].ToLower();
								break;
						}
					}
				}
			}
		}

		private static IEnumerable<string> GetAllCitosVisitedfromLogs(IEnumerable<ItemLog> allLogs)
		{
			var cacheLogs = (
				from log in allLogs
				where (
					(log.LogType == "Dropped Off" || log.LogType == "Visited")
					&&
                    log.Date <= _EndOfRaceDate && log.Date >= _StartOfRaceDate
					)
				select log);

			var citosVisited = new List<string>();

			foreach (var log in cacheLogs)
			{
				var lookupKey = log.Latitude.ToString() + "," + log.Longitude.ToString();

				var gcCode = _CoordInfo[lookupKey].GcCode;

				var isCitoCache = _CacheDB.IsCitoCache(gcCode);
				
				if (isCitoCache)
					citosVisited.Add(gcCode);
			}

			return citosVisited;
		}

        private static IEnumerable<string> GetAllEventsVisitedfromLogs(IEnumerable<ItemLog> allLogs)
        {
            var cacheLogs = (
                from log in allLogs
                where (
                    (log.LogType == "Dropped Off" || log.LogType == "Visited")
                    &&
                    log.Date <= _EndOfRaceDate && log.Date >= _StartOfRaceDate
                    )
                select log);

            var eventsVisited = new List<string>();

            foreach (var log in cacheLogs)
            {
                var lookupKey = log.Latitude.ToString() + "," + log.Longitude.ToString();

                var gcCode = _CoordInfo[lookupKey].GcCode;

                var isEventCache = _CacheDB.IsEventCache(gcCode);

                if (isEventCache)
                    eventsVisited.Add(gcCode);
            }

            return eventsVisited;
        }

		private static IEnumerable<string> GetAllCountryStatesVisited(IEnumerable<ItemLog> allLogs)
		{
			var cacheLogs = (
				from log in allLogs
				where (
					(log.LogType == "Dropped Off" || log.LogType == "Visited")
					&&
                    log.Date <= _EndOfRaceDate && log.Date >= _StartOfRaceDate
					)
				select log);

			var countryAndStates = new List<string>();

			foreach (var log in cacheLogs)
			{
				var countryAndState = GetCountryFromCoOrd((double)log.Latitude, (double)log.Longitude, log.Location, false);

				if (!countryAndStates.Contains(countryAndState))
				{
					countryAndStates.Add(countryAndState);
				}
			}

			return countryAndStates;
		}


		private static void WriteInfoToCsvFile(RaceSummery raceSummery)
		{
			var fileNameAndLocation = GetExecutingPath() + @"\output\TBRaceRaceSummeryCsv.txt";

			TextWriter writer = new StreamWriter(fileNameAndLocation);

			foreach (var prop in raceSummery.GetType().GetProperties())
			{
				if (prop.PropertyType.Name.Contains("IEnumerable"))
				{
					writer.Write(prop.Name + "Count" + _Delimiter);
					writer.Write(prop.Name + _Delimiter);
				}
				else
				{
					writer.Write(prop.Name + _Delimiter);
				}
			}

			writer.WriteLine();

			foreach (var prop in raceSummery.GetType().GetProperties())
			{
				if (prop.PropertyType.Name.Contains("IEnumerable"))
				{
					var prop2 = prop.GetValue(raceSummery, null) as IEnumerable<string>;

					writer.Write(prop2.Count() + _Delimiter);
					writer.Write(String.Join("|", prop2) + _Delimiter);
				}
				else
				{
					writer.Write(prop.GetValue(raceSummery, null) + _Delimiter);
				}
			}

			writer.WriteLine();

			writer.Close();
		}

		private static void WriteInfoToCsvFile(IEnumerable<Trackable> trackables)
		{
			var fileNameAndLocation = GetExecutingPath() + @"\output\TBRaceResultsCsv.txt";

			TextWriter writer = new StreamWriter(fileNameAndLocation);

			var trackableHeading = trackables.First();

			foreach (var prop in trackableHeading.GetType().GetProperties())
			{
				if (prop.PropertyType.Name.Contains("IEnumerable"))
				{
					writer.Write(prop.Name + "Count" + _Delimiter);
					writer.Write(prop.Name + _Delimiter);
				}
				else
				{
					writer.Write(prop.Name + _Delimiter);

				}
			}

			writer.WriteLine();

			foreach (var trackable in trackables)
			{
				foreach (var prop in trackableHeading.GetType().GetProperties())
				{
					if (prop.PropertyType.Name.Contains("IEnumerable"))
					{
						var prop2 = prop.GetValue(trackable, null) as IEnumerable<string>;

						writer.Write(prop2.Count() + _Delimiter);
						writer.Write(String.Join("|", prop2) + _Delimiter);
					}
					else
					{
						writer.Write(prop.GetValue(trackable, null) + _Delimiter);
					}
				}
				writer.WriteLine();
			}

			writer.Close();
		}

		private static void PersistCoOrdInfoToDisk()
		{
			var fileLocation = CoOrdFileNameAndLocation();

			if (File.Exists(fileLocation))
			{
				File.Delete(fileLocation);
			}

			using (var writer = new StreamWriter(fileLocation))
			{
				foreach (var item in _CoordInfo)
				{
					writer.WriteLine(
						String.Format("{0}|{1}|{2}|{3}|{4}|{5}",
						item.Key,
						item.Value.Area,
						item.Value.CountryCode,
						item.Value.CountryName,
						item.Value.State,
						item.Value.GcCode)
					);
				}
			}
		}

		private static string CoOrdFileNameAndLocation()
		{
			return GetExecutingPath() + "\\CoordInfo.txt";
		}

		private static string ConfigFileNameAndLocation()
		{
			return GetExecutingPath() + "\\Configuration.txt";
		}

		private static string GetExecutingPath()
		{
			var exeLocation = System.Reflection.Assembly.GetExecutingAssembly().Location;
			return Path.GetDirectoryName(exeLocation);
		}

		private static void GetCoOrdInfoFromDisk()
		{
			if (File.Exists(CoOrdFileNameAndLocation()))
			{
				using (var reader = new StreamReader(CoOrdFileNameAndLocation()))
				{
					while (!reader.EndOfStream)
					{
						var line = reader.ReadLine();

						if (line != null)
						{
							var values = line.Split('|');

							_CoordInfo.Add(
								values[0],
								new LocationInfo
									{
										Area = values[1],
										CountryCode = values[2],
										CountryName = values[3],
										State = values[4],
										GcCode = values[5]
									}
							);
						}
					}
				}
			}
		}

		private static IEnumerable<string> GetAllCountriesVisited(IEnumerable<ItemLog> allLogs)
		{
			var cacheLogs = (
				from log in allLogs
				where (
					(log.LogType == "Dropped Off" || log.LogType == "Visited")
					&&
                    log.Date <= _EndOfRaceDate && log.Date >= _StartOfRaceDate
					)
				select log);

			var countries = new List<string>();

			foreach (var log in cacheLogs)
			{
				var country = GetCountryFromCoOrd((double)log.Latitude, (double)log.Longitude, log.Location);

				if (!countries.Contains(country))
				{
					countries.Add(country);
				}
			}

			return countries;
		}

		private static string GetCountryFromCoOrd(double latitude, double longitude, string gcCode, bool IsCountryLookup = true)
		{
			var lookupKey = latitude.ToString() + "," + longitude;

			if (_CoordInfo.ContainsKey(lookupKey))
				return IsCountryLookup ? _CoordInfo[lookupKey].CountryCode : _CoordInfo[lookupKey].CountryCode + "-" + _CoordInfo[lookupKey].State;

			var locationInfo = GetLocationInfoFromWebService(latitude, longitude);

			if (locationInfo != null)
			{
				locationInfo.GcCode = gcCode;

				_CoordInfo.Add(lookupKey, locationInfo);
				
				Debug.WriteLine("locationInfo [" + locationInfo + "]");

				return IsCountryLookup ? locationInfo.CountryCode : locationInfo.CountryCode + "-" + locationInfo.State;
			}

			return "Unknown";
		}

		private static LocationInfo GetLocationInfoFromWebService(double latitude, double longitude)
		{
			string url = "http://maps.googleapis.com/maps/api/geocode/xml?latlng={0},{1}&sensor=false";
			//string url = "https://maps.googleapis.com/maps/api/geocode/xml?latlng={0},{1}&sensor=false";
			//string url = "http://maps.google.com/maps/geo?q={0},{1}&output=xml&sensor=false";

			WebResponse response = null;
			try
			{
				var request = (HttpWebRequest)WebRequest.Create(string.Format(url, latitude, longitude));

				//request.Proxy = _Proxy;

				request.Method = "GET";

				response = request.GetResponse();
			}
			catch (Exception ex)
			{
				Console.WriteLine("               ERROR:" + ex.Message);
				return null;
			}

			XmlDocument doc = new XmlDocument();

			doc.Load(response.GetResponseStream());

			string countryCode = string.Empty;
			string countryName = string.Empty;
			string state = string.Empty;
			string area = string.Empty;

			if (doc.SelectNodes("/GeocodeResponse/result[type='country']").Count > 0)
			{
				countryCode = doc.SelectSingleNode("/GeocodeResponse/result[type='country']/address_component/short_name").InnerText;
				countryName = doc.SelectSingleNode("/GeocodeResponse/result[type='country']/address_component/long_name").InnerText;

				if (doc.SelectNodes("/GeocodeResponse/result[type='administrative_area_level_1']").Count > 0)
					state = doc.SelectSingleNode("/GeocodeResponse/result[type='administrative_area_level_1']/address_component/long_name").InnerText;
				else
					state = "Unknown";

				if (doc.SelectNodes("/GeocodeResponse/result[type='locality']/address_component[type='locality']").Count > 0)
					area = doc.SelectSingleNode("/GeocodeResponse/result[type='locality']/address_component[type='locality']/long_name").InnerText;
				else
					area = "Unknown";
			}
			else
			{
				var countryCodeDoc = doc.SelectSingleNode("/GeocodeResponse/result[type='street_address']/address_component[type='country']/short_name");
				if (countryCodeDoc != null)
					countryCode = countryCodeDoc.InnerText;
				else
					countryCode = "Unknown";

				var countryNameDoc =doc.SelectSingleNode("/GeocodeResponse/result[type='street_address']/address_component[type='country']/long_name");
				if (countryNameDoc != null)
					countryName = countryNameDoc.InnerText;
				else
					countryName = "Unknown";

					var stateDoc = doc.SelectSingleNode("/GeocodeResponse/result[type='street_address']/address_component[type='administrative_area_level_1']/long_name");
				if (stateDoc != null)
					state = stateDoc.InnerText;
				else
					state= "Unknown";

				var areaDoc = doc.SelectSingleNode("/GeocodeResponse/result[type='street_address']/address_component[type='locality']/long_name");
				if (areaDoc != null)
					area = areaDoc.InnerText;
				else
					area = "Unknown";
			}

			//-------------
			// LocationInfo
			//if (doc.SelectNodes("/GeocodeResponse/result[type='country']").Count > 0)
			//{
			//countryCode = doc.SelectSingleNode("/GeocodeResponse/result[type='country']/address_component/short_name").InnerText;
			//countryName = doc.SelectSingleNode("/GeocodeResponse/result[type='country']/address_component/long_name").InnerText;
			//}
			//else
			//{
			//    countryCode = doc.SelectSingleNode("country").InnerText;
			//    countryName = doc.SelectSingleNode("AddressLine").InnerText;
			//}

			//string state = doc.GetElementsByTagName("administrative_area_level_1")[0] != null ? doc.GetElementsByTagName("AdministrativeAreaName")[0].InnerText : "Unknown";
			//string area = doc.GetElementsByTagName("SubAdministrativeAreaName")[0] != null ? doc.GetElementsByTagName("SubAdministrativeAreaName")[0].InnerText : "Unknown";
			//-------------------
			////-------------
			//// LocationInfo
			//if (doc.GetElementsByTagName("CountryNameCode").Count > 0)
			//{
			//    countryCode = doc.GetElementsByTagName("CountryNameCode")[0].InnerText;
			//    countryName = doc.GetElementsByTagName("CountryName")[0].InnerText;
			//}
			//else
			//{
			//    countryCode = doc.GetElementsByTagName("address")[0].InnerText;
			//    countryName = doc.GetElementsByTagName("AddressLine")[0].InnerText;
			//}

			//string state = doc.GetElementsByTagName("AdministrativeAreaName")[0] != null ? doc.GetElementsByTagName("AdministrativeAreaName")[0].InnerText : "Unknown";
			//string area = doc.GetElementsByTagName("SubAdministrativeAreaName")[0] != null ? doc.GetElementsByTagName("SubAdministrativeAreaName")[0].InnerText : "Unknown";
			////-------------------

			return new LocationInfo
				{
					Area = area,
					CountryCode = countryCode,
					CountryName = countryName,
					State = state
				};
		}

		private static int CalculateTimesDiscoveredFromLogs(IEnumerable<ItemLog> allLogs)
		{
			return (
				from log in allLogs
				where (log.LogType == "Discovered It" && log.Date <= _EndOfRaceDate && log.Date >= _StartOfRaceDate)
				select log)
			.Count();
		}

		private static int CalculateCachesVisitedFromLogs(IEnumerable<ItemLog> allLogs)
		{
			return (
				from log in allLogs
				where (
					(log.LogType == "Dropped Off" || log.LogType == "Visited")
					&&
                    log.Date <= _EndOfRaceDate && log.Date >= _StartOfRaceDate
					)
				select log)
			.Count();
		}

        private static int CalculateCachesDroppedInFromLogs(IEnumerable<ItemLog> allLogs)
        {
            return (
                from log in allLogs
                where (
                    (log.LogType == "Dropped Off")
                    &&
                    log.Date <= _EndOfRaceDate && log.Date >= _StartOfRaceDate
                    )
                select log)
            .Count();
        }

        private static int CalculateCachesDroppedInDifficultFromLogs(IEnumerable<ItemLog> allLogs)
        {
            return (
                from log in allLogs
                where (
                    (log.LogType == "Dropped Off")
                    &&
                    log.Date <= _EndOfRaceDate && log.Date >= _StartOfRaceDate
                    )
                select log)
            .Count();
        }

		private static Tuple<double,string> CalculateSinglePersonDropDistanceFromLogs(IEnumerable<ItemLog> allLogs)
		{
			var cachesDroppedInto = new List<string>();

			int i = 1;

			var cachersHandeld = new List<string>();

			var droppedLogs = (
				from log in allLogs
				where (
					(log.LogType == "Dropped Off" || log.LogType == "Visited") 
					&&
                    log.Date <= _EndOfRaceDate && log.Date >= _StartOfRaceDate
					)
				select log).OrderBy(logs => logs.Date).ThenBy(logs => logs.LogId);

			double totalDistance = 0;

			double prevLat = 0;
			double prevLon = 0;

			foreach (var log in droppedLogs)
			{
				if (!cachersHandeld.Contains(log.ByCacher))
				{
					if (prevLat == 0)
					{
						prevLat = Double.Parse(log.Latitude.ToString());
						prevLon = Double.Parse(log.Longitude.ToString());

						cachesDroppedInto.Add(i++ + ". " + log.Location);
					}
					else
					{
						var lat = Double.Parse(log.Latitude.ToString());
						var lon = Double.Parse(log.Longitude.ToString());

						var distance = Helper.HaversineInKM(
							prevLat, prevLon,
							lat,
							lon
							);

						totalDistance += distance;

						prevLat = lat;
						prevLon = lon;

						cachersHandeld.Add(log.ByCacher);
						cachesDroppedInto.Add(i++ + ". " + log.Location);
					}
				}

			}

			return new Tuple<double, string>(totalDistance, String.Join(", ", cachesDroppedInto));
		}

		private static double CalculateTotalDisTanceFromLogs(IEnumerable<ItemLog> allLogs)
		{
			var droppedLogs = (
				from log in allLogs
				where (
					(log.LogType == "Dropped Off" || log.LogType == "Visited")
					&&
					log.Date <= _EndOfRaceDate && log.Date >= _StartOfRaceDate
					)
				select log).OrderBy(logs => logs.Date).ThenBy(logs => logs.LogId);

			double totalDistance = 0;

			double prevLat = 0;
			double prevLon = 0;

			foreach (var log in droppedLogs)
			{
				if (prevLat == 0)
				{
					prevLat = Double.Parse(log.Latitude.ToString());
					prevLon = Double.Parse(log.Longitude.ToString());
				}
				else
				{
					var lat = Double.Parse(log.Latitude.ToString());
					var lon = Double.Parse(log.Longitude.ToString());

					var distance = Helper.HaversineInKM(
						prevLat, prevLon,
						lat,
						lon
					);

					totalDistance += distance;

					prevLat = lat;
					prevLon = lon;
				}
			}

			return totalDistance;
		}

		private static IEnumerable<ItemLog> GetAllLogsForTrackable(string code)
		{
			return SQLiteDatabase.GetAllLogs(code, _TBDatabase);
		}

		private static IEnumerable<Trackable> GetAllTbs()
		{
			return SQLiteDatabase.GetAllTBs(_TBDatabase);
		}
	}
}
